package com.dp.service.menucontrol;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.NoResultException;
import javax.persistence.NonUniqueResultException;
import javax.persistence.Query;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.dp.pojo.JsonPage;
import com.dp.pojo.SnAreaVo;
import com.dp.service.impl3.BaseImpl;
import com.dp.util.DataMap;
import com.dp.vo.NewsGroup;
import com.dp.vo.NewsK16server;
import com.dp.vo.NewsK16serverSn;
import com.dp.vo.NewsSnArea;
import com.dp.vo.TAreainfo;

@Service
@Transactional
public class K16serversnServiceImpl extends BaseImpl {
	/**
	 * Find all the findAllK16serverSnEntity Entity
	 * @param hashMap
	 * @param firstrow
	 * @param maxrow
	 * @return List<NewsK16serverSn>
	 */
	public List<NewsK16serverSn> findAllK16serverSnEntity(DataMap hashMap, Integer firstrow,
			Integer maxrow){
		JsonPage jpage=(JsonPage) hashMap.get("jpage");
		NewsK16serverSn k16serversn=(NewsK16serverSn) hashMap.get("k16serversn");
		StringBuffer sqlS = new StringBuffer("SELECT n FROM NewsK16serverSn n WHERE 1=1 ");
		if(k16serversn!=null){
			if(k16serversn.getSn()!=null&& k16serversn.getSn().toString().trim().length() > 0){
				sqlS.append(" AND n.sn like '%"
						+ k16serversn.getSn().toString().trim() + "%'");
			}
			if(k16serversn.getK16server().getId()!=null&& k16serversn.getK16server().getId().toString().trim().length() > 0){
				sqlS.append(" AND n.k16server.id="
						+ k16serversn.getK16server().getId().trim() + "");
			}
		}
		
		if(jpage!=null){
			if(jpage.getSidx()!=null&&!"".equals(jpage.getSidx())){
				sqlS.append(" order by n."+jpage.getSidx()+" "+jpage.getSord());
			}
		}
		List<NewsK16serverSn> queryList=new ArrayList<NewsK16serverSn>();
		try {
			Query query = em.createQuery(sqlS.toString());
			if (firstrow != null) {
				query.setFirstResult(firstrow);
			}
			if (maxrow != null) {
				query.setMaxResults(maxrow);
			}
			queryList = query.getResultList();
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return queryList;
	}
	
	/**
	 * SN与服务器映射 记录数
	 * @param hashMap
	 * @return Integer
	 */
	public Integer findAllK16serverSnEntityCount(DataMap hashMap){
		Integer iCount = 0; 
		NewsK16serverSn k16serversn=(NewsK16serverSn) hashMap.get("k16serversn");
		StringBuffer sqlS = new StringBuffer("SELECT count(n.sn) FROM NewsK16serverSn n WHERE 1=1 ");
		if(k16serversn!=null){
			if(k16serversn.getSn()!=null&& k16serversn.getSn().toString().trim().length() > 0){
				sqlS.append(" AND n.sn like '%"
						+ k16serversn.getSn().toString().trim() + "%'");
			}
			if(k16serversn.getK16server().getId()!=null&& k16serversn.getK16server().getId().toString().trim().length() > 0){
				sqlS.append(" AND n.k16server.id="
						+ k16serversn.getK16server().getId().trim() + "");
			}
		}
		Query query=null;
		try {
			query = em.createQuery(sqlS.toString());
			iCount = Integer.valueOf(query.getSingleResult().toString());
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return iCount;
	}
	
	/**
	 * SN与区域对应信息总记录数
	 * @param hashMap
	 * @return Integer
	 */
	public Integer findAllSnAreaEntityCount(DataMap hashMap){
		NewsSnArea snarea=(NewsSnArea) hashMap.get("snarea");
		NewsGroup group=(NewsGroup) hashMap.get("group");
		String areaids=(String) hashMap.get("areaids");
		String selectedSet=(String) hashMap.get("selectedSet");
		Integer iCount = 0; 
		StringBuffer sqlS = new StringBuffer("SELECT count(n.newsSnAreaPK.sn) FROM NewsSnArea n WHERE 1=1 ");
		if(snarea!=null){
			if(snarea.getNewsSnAreaPK().getSn()!=null&& snarea.getNewsSnAreaPK().getSn().toString().trim().length() > 0){
				sqlS.append(" AND n.newsSnAreaPK.sn like '%"
						+ snarea.getNewsSnAreaPK().getSn().toString().trim() + "%'");
			}
		}
		if(areaids!=null){
			sqlS.append(" AND n.newsSnAreaPK.areainfo.areaid in ("
					+ areaids + ")");
		}
		try {
			Query query = em.createQuery(sqlS.toString());
			iCount = Integer.valueOf(query.getSingleResult().toString());
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return iCount;
	}
	
	/**
	 * Find all the NewsSnArea Entity
	 * @param hashMap
	 * @param firstrow
	 * @param maxrow
	 * @return List<SnAreaVo>
	 */
	public List<SnAreaVo> findAllSnAreaEntity(DataMap hashMap, Integer firstrow,
			Integer maxrow){
		JsonPage jpage=(JsonPage) hashMap.get("jpage");
		NewsSnArea snarea=(NewsSnArea) hashMap.get("snarea");
		String areaids=(String) hashMap.get("areaids");
		StringBuffer sqlS = new StringBuffer("SELECT n FROM NewsSnArea n WHERE 1=1 ");
		if(snarea!=null){
			if(snarea.getNewsSnAreaPK().getSn()!=null&& snarea.getNewsSnAreaPK().getSn().toString().trim().length() > 0){
				sqlS.append(" AND n.newsSnAreaPK.sn like '%"
						+ snarea.getNewsSnAreaPK().getSn().toString().trim() + "%'");
			}
		}
		if(areaids!=null){
			sqlS.append(" AND n.newsSnAreaPK.areainfo.areaid in ("
					+ areaids + ")");
		}
		
		if(jpage!=null){
			if(jpage.getSidx()!=null&&!"".equals(jpage.getSidx())){
				sqlS.append(" order by n."+jpage.getSidx()+" "+jpage.getSord());
			}
		}
		List<SnAreaVo> list=new ArrayList<SnAreaVo>();
		try {
			Query query = em.createQuery(sqlS.toString());
			if (firstrow != null) {
				query.setFirstResult(firstrow);
			}
			if (maxrow != null) {
				query.setMaxResults(maxrow);
			}
			List<NewsSnArea> queryList=new ArrayList<NewsSnArea>();
			queryList = query.getResultList();
			SnAreaVo snAreaVo = null;
			for(NewsSnArea snarea_temp:queryList){
				snAreaVo=new SnAreaVo();
				snAreaVo.setSn(snarea_temp.getNewsSnAreaPK().getSn());
				snAreaVo.setAreaid(snarea_temp.getNewsSnAreaPK().getAreainfo().getAreaid()+"");
				snAreaVo.setAreaname(snarea_temp.getNewsSnAreaPK().getAreainfo().getAreaname());
				list.add(snAreaVo);
			}
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return list;
	}
	
	
	/**
	 * 服务器列表
	 * @return
	 */
	public List<NewsK16server> findAllK16serverEntity(){
		StringBuffer sqlS = new StringBuffer("SELECT n FROM NewsK16server n WHERE 1=1 ");
		List<NewsK16server> queryList=new ArrayList<NewsK16server>();
		try {
			Query query = em.createQuery(sqlS.toString());
			queryList = query.getResultList();
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return queryList;
	}
	
	//新增
	public Boolean addK16serversnE(NewsK16serverSn k16serversn) {
		Boolean resultb=true;
		try {
			String jpql="SELECT n FROM NewsK16serverSn n WHERE n.sn=:sn";
			Query query = em.createQuery(jpql);
			query.setParameter("sn",k16serversn.getSn());
			NewsK16serverSn k16serversn_temp= (NewsK16serverSn) getSingleResult(query);
			if(k16serversn_temp!=null){
				resultb=null;
			}else{
				em.persist(k16serversn);
			}
		} catch (Exception e) {
			logger.error("addK16serversnE fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
		}
		return resultb;
	}
	
	//修改
	public Boolean updateK16serversnE(NewsK16serverSn k16serversn,NewsK16serverSn k16serversnO) {
		Boolean resultb=true;
		try {
			String jpql="SELECT n FROM NewsK16serverSn n WHERE n.sn=:sn ";
			Query query = em.createQuery(jpql);
			query.setParameter("sn",k16serversn.getSn());
			NewsK16serverSn k16serversn_temp= (NewsK16serverSn) getSingleResult(query);
			boolean updateD=false;
			if(k16serversn_temp!=null){
				if(k16serversn_temp.getSn().equals(k16serversnO.getSn())){
					updateD=true;
				}else{
					resultb=null;
				}
			}else{
				updateD=true;
			}
			if(updateD){
				jpql="UPDATE NewsK16serverSn n SET n.sn=:sn,n.k16server.id=:serverid WHERE n.sn=:oldsn";
				query = em.createQuery(jpql);
				query.setParameter("sn",k16serversn.getSn());
				query.setParameter("serverid", k16serversn.getK16server().getId());
				query.setParameter("oldsn", k16serversnO.getSn());
				query.executeUpdate();
			}
		} catch (Exception e) {
			logger.error("updateK16serversnE fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
		}
		return resultb;
	}
	
	//删除
	public boolean deleteK16serversnEByIds(String ids) {
		Boolean resultb=true;
		try {
			
			String[] idarys=ids.split(",");
			this.sql="delete from news_k16serversn where sn=?";
			Query	query=em.createNativeQuery(sql);
			
			
			for(String sn:idarys){
				query.setParameter(1, sn);
				query.executeUpdate();
			}
		} catch (Exception e) {
			logger.error("deleteK16serversnEByIds fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
		}
		return resultb;
	}
	public Boolean addK16serversnEL(DataMap hashMap) {
		int target_server=Integer.parseInt(hashMap.get("target_server").toString()) ;
		List<SnAreaVo> salist= (List<SnAreaVo>)hashMap.get("salist");
		String strIdList = (String) hashMap.get("strIdList");
		Boolean resultb=true;
		String [] strIdListarys=null;
		try {
			
			
			if(salist!=null){
				//全部加入
				strIdListarys=new String[salist.size()];
				for(int i=0;i<salist.size();i++){
					strIdListarys[i]=salist.get(i).getSn()+"";
				}
			}else if(strIdList!=null&&!"".equals(strIdList)){
				//加入,一个或多个
				strIdListarys= strIdList.split(",");
			}else{
				return true;
			}
			
		
			for (String sn : strIdListarys) {
				this.sql="select count(*) from news_k16serversn where sn='"+sn+"'";
				if(this.getOneInt()>0){
					sql="update news_k16serversn set  serverid="+target_server+" where sn='"+sn+"'";
				   
				}else{
					sql="insert into news_k16serversn (sn, serverid) values ('"+sn+"', "+target_server+")";
				}
				 this.em.createNativeQuery(sql).executeUpdate();
			}
		
		} catch (Exception e) {
			logger.info("addK16serversnEL fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
		}
		return resultb;
	}
	
	/**
	 * 预处理得到检索条件中所有的areaid
	 * @param area_id
	 * @return
	 */
	public String findAllAreaids(Integer area_id) {
		String areaids=null;
    	try {
	    	StringBuffer sqlS = new StringBuffer("SELECT p FROM TAreainfo p");
	        Query query = em.createQuery(sqlS.toString());  
	        List<TAreainfo> queryList=new ArrayList<TAreainfo>();
	        queryList=query.getResultList();  
	        if(queryList.size()>0){
	        	List<Integer> areaid_templist=new ArrayList<Integer>();
	        	areaid_templist = getAreaid_templist(queryList,area_id,areaid_templist);
	        	areaids="";
	        	for(Integer areaid_temp:areaid_templist){
	        		areaids+="'"+areaid_temp+"',";
	        	}
	        	if(!"".equals(areaids)){
	        		areaids=areaids.substring(0, areaids.length()-1);
	        	}
	        }
	        
    	} catch (Exception e) {
			e.printStackTrace();
		}
		return areaids;
	}
	
	private List<Integer> getAreaid_templist(List<TAreainfo> tailist,int areaid_temp,List<Integer> areaid_templist){
		areaid_templist.add(areaid_temp);
		for(TAreainfo tc3:tailist){
			if(tc3.getUpareaid().equals(areaid_temp)){
				if(tc3.getAreaid()!=0){
					getAreaid_templist(tailist,tc3.getAreaid(),areaid_templist);
				}
			}
		}
		return areaid_templist;
	}
	
	public Object getSingleResult(Query query) {
		Object o =null;
		try {
			o = query.getSingleResult();
		} catch (NoResultException ex) {
			return null;
		} catch (NonUniqueResultException ex) {
			o = query.getResultList();
			return ((List) o).get(0);
		}
		return o;
	}

	public List<TAreainfo> findAllAreainfo(int upareaid) {
		List<TAreainfo> queryList=new ArrayList<TAreainfo>();
    	try {
	    	StringBuffer sqlS = new StringBuffer("SELECT p FROM TAreainfo p WHERE p.upareaid=:upareaid ");
	    	sqlS.append(" order by p.areaid");
	        Query query = em.createQuery(sqlS.toString());      
	        query.setParameter("upareaid", upareaid);
	        queryList=query.getResultList();  
    	} catch (Exception e) {
			e.printStackTrace();
		}
        return queryList;
	}

}
